DROP TABLE customer      CASCADE CONSTRAINTS;
DROP TABLE book          CASCADE CONSTRAINTS;
DROP TABLE orders        CASCADE CONSTRAINTS;
DROP TABLE imported_book CASCADE CONSTRAINTS;
purge recyclebin;

CREATE TABLE customer (
    custid  NUMBER(2) NOT NULL,
    name    VARCHAR2(40),
    address VARCHAR2(50),
    phone   VARCHAR2(20)
);
ALTER TABLE customer ADD CONSTRAINT customer_pk PRIMARY KEY ( custid );

CREATE TABLE book (
    bookid    NUMBER(2) NOT NULL,
    bookname  VARCHAR2(40),
    publisher VARCHAR2(40),
    price     NUMBER(8)
);
ALTER TABLE book ADD CONSTRAINT book_pk PRIMARY KEY ( bookid );

CREATE TABLE orders (
    orderid   NUMBER(2) NOT NULL,
    custid    NUMBER(2) NOT NULL,
    bookid    NUMBER(2) NOT NULL,
    saleprice NUMBER(8),
    orderdate DATE
);
ALTER TABLE orders ADD CONSTRAINT orders_pk PRIMARY KEY ( orderid );

CREATE TABLE imported_book (
    bookid    NUMBER(2),
    bookname  VARCHAR2(40),
    publisher VARCHAR2(40),
    price     NUMBER(8)
);

ALTER TABLE orders
    ADD CONSTRAINT orders_book_fk FOREIGN KEY ( bookid )
        REFERENCES book ( bookid );
ALTER TABLE orders
    ADD CONSTRAINT orders_customer_fk FOREIGN KEY ( custid )
        REFERENCES customer ( custid );